﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using SpreadsheetLight;
using KSSModel;
using System.Data.Linq;


namespace HRDBImport
{
    class Program
    {
        static void Main(string[] args)
        {
            List<tblEMPBank> hrBanks = new List<tblEMPBank>(); ;
            Console.WriteLine("Importing HR Database, Please do not close the windows.");
            //Console.ReadLine();
            var hrLastest = Directory.GetFiles("C:\\HRDB")
                .Select(x => new FileInfo(x))
                .OrderByDescending(x => x.LastWriteTime)
                .First();
            if (hrLastest != null)
            {
                hrBanks = new List<tblEMPBank>();
                var hr = ReadExcel(hrLastest.FullName, ref hrBanks);

                using (var dc = new KSSDBDataContext())
                {
                    foreach (var edmshr in hr)
                    {
                        bool isExits = false;
                        foreach (var tblEdmshr in dc.tblEDMSHRs)
                        {
                            if (tblEdmshr.mstaffno == edmshr.mstaffno)
                            {
                                tblEdmshr.mfac = edmshr.mfac;
                                tblEdmshr.mno = edmshr.mno;
                                tblEdmshr.mstaffno = edmshr.mstaffno;
                                tblEdmshr.mtitleTh = edmshr.mtitleTh;
                                tblEdmshr.mnameTh = edmshr.mnameTh;
                                tblEdmshr.surnameTh = edmshr.surnameTh;
                                tblEdmshr.mpositionTh = edmshr.mpositionTh;
                                tblEdmshr.sacnameTh = edmshr.sacnameTh;
                                tblEdmshr.depnameTh = edmshr.depnameTh;
                                tblEdmshr.divnameTh = edmshr.divnameTh;
                                tblEdmshr.divname2Th = edmshr.divname2Th;
                                tblEdmshr.mpl = edmshr.mpl;
                                tblEdmshr.mfacName = edmshr.mfacName;
                                tblEdmshr.mlocation = edmshr.mlocation;
                                tblEdmshr.mtitleEn = edmshr.mtitleEn;
                                tblEdmshr.mnameEn = edmshr.mnameEn;
                                tblEdmshr.msurnameEn = edmshr.msurnameEn;
                                tblEdmshr.mpositionEn = edmshr.mpositionEn;
                                tblEdmshr.sectionEn = edmshr.sectionEn;
                                tblEdmshr.depnameEn = edmshr.depnameEn;
                                tblEdmshr.divnameEn = edmshr.divnameEn;
                                tblEdmshr.divname2En = edmshr.divname2En;
                                tblEdmshr.costCenter = edmshr.costCenter;
                                tblEdmshr.memail = edmshr.memail;
                                tblEdmshr.Birthday = edmshr.Birthday;
                                tblEdmshr.StartDay = edmshr.StartDay;
                                tblEdmshr.PID = edmshr.PID;
                                isExits = true;
                            }
                        }
                        if (!isExits)
                        {
                            dc.tblEDMSHRs.InsertOnSubmit(edmshr);
                        }
                        bool isUserExites = false;
                        foreach (var tblUser in dc.tblUsers)
                        {
                            if (edmshr.mstaffno == tblUser.UserName)
                            {
                                tblUser.FirstName = edmshr.mnameTh;
                                tblUser.Lastname = edmshr.surnameTh;
                                tblUser.Email = edmshr.memail;
                                tblUser.CreateDate = DateTime.Now;
                                isUserExites = true;
                            }
                        }
                        if (!isUserExites)
                        {
                            dc.tblUsers.InsertOnSubmit(new tblUser
                            {
                                UserName = edmshr.mstaffno,
                                FirstName = edmshr.mnameTh,
                                Lastname = edmshr.surnameTh,
                                Email = edmshr.memail,
                                CreateDate = DateTime.Now
                            });
                        }
                    }
                    dc.SubmitChanges();
                }

                using (var dc = new EDMSDBDataContext())
                {
                    foreach (var edmshr in hr)
                    {
                        bool isExits = false;
                        foreach (var tblEdmshr in dc.tblEDMS)
                        {
                            if (tblEdmshr.mstaffno == edmshr.mstaffno)
                            {
                                tblEdmshr.mfac = edmshr.mfac;
                                tblEdmshr.mno = edmshr.mno;
                                tblEdmshr.mstaffno = edmshr.mstaffno;
                                tblEdmshr.mtitleTh = edmshr.mtitleTh;
                                tblEdmshr.mnameTh = edmshr.mnameTh;
                                tblEdmshr.surnameTh = edmshr.surnameTh;
                                tblEdmshr.mpositionTh = edmshr.mpositionTh;
                                tblEdmshr.sacnameTh = edmshr.sacnameTh;
                                tblEdmshr.depnameTh = edmshr.depnameTh;
                                tblEdmshr.divnameTh = edmshr.divnameTh;
                                tblEdmshr.divname2Th = edmshr.divname2Th;
                                tblEdmshr.mpl = edmshr.mpl;
                                tblEdmshr.mfacName = edmshr.mfacName;
                                tblEdmshr.mlocation = edmshr.mlocation;
                                tblEdmshr.mtitleEn = edmshr.mtitleEn;
                                tblEdmshr.mnameEn = edmshr.mnameEn;
                                tblEdmshr.msurnameEn = edmshr.msurnameEn;
                                tblEdmshr.mpositionEn = edmshr.mpositionEn;
                                tblEdmshr.sectionEn = edmshr.sectionEn;
                                tblEdmshr.depnameEn = edmshr.depnameEn;
                                tblEdmshr.divnameEn = edmshr.divnameEn;
                                tblEdmshr.divname2En = edmshr.divname2En;
                                tblEdmshr.costCenter = edmshr.costCenter;
                                tblEdmshr.memail = edmshr.memail;
                                tblEdmshr.Birthday = edmshr.Birthday;
                                tblEdmshr.StartDay = edmshr.StartDay;
                                tblEdmshr.PID = edmshr.PID;
                                isExits = true;
                            }
                        }
                        if (!isExits)
                        {
                            var newEdms = new tblEDMS();
                            newEdms.mfac = edmshr.mfac;
                            newEdms.mno = edmshr.mno;
                            newEdms.mstaffno = edmshr.mstaffno;
                            newEdms.mtitleTh = edmshr.mtitleTh;
                            newEdms.mnameTh = edmshr.mnameTh;
                            newEdms.surnameTh = edmshr.surnameTh;
                            newEdms.mpositionTh = edmshr.mpositionTh;
                            newEdms.sacnameTh = edmshr.sacnameTh;
                            newEdms.depnameTh = edmshr.depnameTh;
                            newEdms.divnameTh = edmshr.divnameTh;
                            newEdms.divname2Th = edmshr.divname2Th;
                            newEdms.mpl = edmshr.mpl;
                            newEdms.mfacName = edmshr.mfacName;
                            newEdms.mlocation = edmshr.mlocation;
                            newEdms.mtitleEn = edmshr.mtitleEn;
                            newEdms.mnameEn = edmshr.mnameEn;
                            newEdms.msurnameEn = edmshr.msurnameEn;
                            newEdms.mpositionEn = edmshr.mpositionEn;
                            newEdms.sectionEn = edmshr.sectionEn;
                            newEdms.depnameEn = edmshr.depnameEn;
                            newEdms.divnameEn = edmshr.divnameEn;
                            newEdms.divname2En = edmshr.divname2En;
                            newEdms.costCenter = edmshr.costCenter;
                            newEdms.memail = edmshr.memail;
                            newEdms.Birthday = edmshr.Birthday;
                            newEdms.StartDay = edmshr.StartDay;
                            newEdms.PID = edmshr.PID;
                            dc.tblEDMS.InsertOnSubmit(newEdms);
                        }
                    }
                    dc.SubmitChanges();
                }
            }
            Console.WriteLine("Completed Import HR Database.");




            using (var dbe = new EDMSDBDataContext())
            {

                foreach (var tblEmpBank in hrBanks)
                {
                    var exitsEmp = dbe.tblEMPBanks.Where(e => e.mstaffno == tblEmpBank.mstaffno);
                    if (exitsEmp.Any())
                    {
                        exitsEmp.First().mnameTh = tblEmpBank.mnameTh;
                        exitsEmp.First().msurnameTh = tblEmpBank.msurnameTh;
                        exitsEmp.First().bankId = tblEmpBank.bankId;
                        exitsEmp.First().empbankacctno = tblEmpBank.empbankacctno;
                        exitsEmp.First().isDisable = tblEmpBank.isDisable;
                    }
                    else
                    {
                        dbe.tblEMPBanks.InsertOnSubmit(tblEmpBank);
                    }
                }

                dbe.SubmitChanges();
            }

            //var hrBankLastest = Directory.GetFiles("C:\\BankAccount")
            //    .Select(x => new FileInfo(x))
            //    .OrderByDescending(x => x.LastWriteTime)
            //    .First();
            //Console.WriteLine("Start importing HR Bank Account Data.");
            //if (hrBankLastest != null)
            //{
            //    ImportCsv(hrBankLastest.FullName);
            //}
            //Console.WriteLine("Completed import HR Bank Account Data.");

        }
        public static List<tblEDMSHR> ReadExcel(string path, ref List<tblEMPBank> hrBanks)
        {
            var sl = new SLDocument(path, "16-08-2015");
            var test = sl.GetCells();
            var k = test;
            var edms = new tblEDMSHR();
            var hrs = new List<tblEDMSHR>();

            GetExcelRow(ref sl, ref hrs, ref hrBanks, 2);

            return hrs;
        }

        static bool GetExcelRow(ref SLDocument doc, ref List<tblEDMSHR> edmss, ref List<tblEMPBank> emdBanks, int row)
        {
            var mfac = doc.GetCellValueAsString(row, 1);
            var mno = "";//doc.GetCellValueAsString(row, 2);
            var mstaffno = doc.GetCellValueAsString(row, 2).Replace("-", "");

            while (!string.IsNullOrEmpty(mfac) && !string.IsNullOrEmpty(mstaffno))
            {
                var mtitleTh = doc.GetCellValueAsString(row, 3);
                var mnameTh = doc.GetCellValueAsString(row, 4);
                var surnameTh = doc.GetCellValueAsString(row, 5);
                var mpositionTh = doc.GetCellValueAsString(row, 6);
                var sacnameTh = doc.GetCellValueAsString(row, 7);
                var depnameTh = doc.GetCellValueAsString(row, 9);
                var divnameTh = doc.GetCellValueAsString(row, 10);
                var divname2Th = doc.GetCellValueAsString(row, 11);
                var mpl = doc.GetCellValueAsString(row, 14);

                var mtitleEn = doc.GetCellValueAsString(row, 15);
                var mnameEn = doc.GetCellValueAsString(row, 16);
                var msurnameEn = doc.GetCellValueAsString(row, 17);
                var mpositionEn = doc.GetCellValueAsString(row, 18);
                var sectionEn = doc.GetCellValueAsString(row, 19);
                var depnameEn = doc.GetCellValueAsString(row, 20);
                var divnameEn = doc.GetCellValueAsString(row, 21);
                var divname2En = doc.GetCellValueAsString(row, 22);
                var mlocation = doc.GetCellValueAsString(row, 25);
                var costCenter = doc.GetCellValueAsString(row, 26);
                var mbirtDate = doc.GetCellValueAsString(row, 27);
                var mstartDate = doc.GetCellValueAsString(row, 28);
                var mfacName = "";//doc.GetCellValueAsString(row, 16);
                var pid = doc.GetCellValueAsString(row, 29);
                var memail = doc.GetCellValueAsString(row, 30);


                var bankname = doc.GetCellValueAsString(row, 31);
                var bankId = doc.GetCellValueAsString(row, 33);
                var bankAccount = doc.GetCellValueAsString(row, 34);

                //var mapplevel = doc.GetCellValueAsString(row, 25);
                edmss.Add(new tblEDMSHR
                {
                    mfac = mfac,
                    mno = mno,
                    mstaffno = mstaffno,
                    mtitleTh = mtitleTh,
                    mnameTh = mnameTh,
                    surnameTh = surnameTh,
                    mpositionTh = mpositionTh,
                    sacnameTh = sacnameTh,
                    depnameTh = depnameTh,
                    divnameTh = divnameTh,
                    divname2Th = divname2Th,
                    mpl = mpl,

                    mfacName = mfacName,
                    mlocation = mlocation,
                    mtitleEn = mtitleEn,
                    mnameEn = mnameEn,
                    msurnameEn = msurnameEn,
                    mpositionEn = mpositionEn,
                    sectionEn = sectionEn,
                    depnameEn = depnameEn,
                    divnameEn = divnameEn,
                    divname2En = divname2En,
                    costCenter = costCenter,
                    memail = memail,
                    //Birthday = mbirtDate,
                    //StartDay = mstartDate,
                    //mapplevel = mapplevel,
                    mtypeid = 0,
                    PID = pid
                });

                emdBanks.Add(new tblEMPBank()
                {
                    bankId = bankId,
                    empbankacctno = bankAccount,
                    isDisable = false,
                    mnameTh = mnameTh,
                    mstaffno = mstaffno,
                    msurnameTh = surnameTh
                });
                return GetExcelRow(ref doc, ref edmss, ref emdBanks, row + 1);
            }
            return false;
        }
        static void ImportCsv(string filePath)
        {
            var test = File.ReadAllLines(filePath, Encoding.UTF8);
            var allEmp = test.Skip(1).Select(s => s.Split(',')).Select(e => new tblEMPBank
            {
                mstaffno = e[0].Replace("\"", ""),
                mnameTh = e[1].Replace("\"", ""),
                msurnameTh = e[2].Replace("\"", ""),
                bankId = !string.IsNullOrEmpty(e[3]) && e[3].Length >= 14 ? e[3].Substring(1, 3) : "",
                empbankacctno = !string.IsNullOrEmpty(e[3]) && e[3].Length >= 14 ? e[3].Substring(4, 10) : ""
            }).ToList();


            using (var dbe = new EDMSDBDataContext())
            {
                foreach (var tblEmpBank in allEmp)
                {
                    var exitsEmp = dbe.tblEMPBanks.Where(e => e.mstaffno == tblEmpBank.mstaffno);
                    if (exitsEmp.Any())
                    {
                        exitsEmp.First().mnameTh = tblEmpBank.mnameTh;
                        exitsEmp.First().msurnameTh = tblEmpBank.msurnameTh;
                        exitsEmp.First().bankId = tblEmpBank.bankId;
                        exitsEmp.First().empbankacctno = tblEmpBank.empbankacctno;
                        exitsEmp.First().isDisable = tblEmpBank.isDisable;
                    }
                    else
                    {
                        dbe.tblEMPBanks.InsertOnSubmit(tblEmpBank);
                    }
                }

                dbe.SubmitChanges();
            }
        }
    }
}
